﻿using SqlKata.Execution;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using U8WebService.Models;

namespace U8WebService.Common
{
    public class DbHelper
    {
        /// <summary>
        /// 数据库多表事务提交方法
        /// </summary>
        /// <param name="list"></param>
        /// <param name="dbName"></param>
        public ReturnResult TransactionSample(List<DbTable> list)
        {

            ReturnResult rsl = new ReturnResult();
            var u8Db = new Katabase().U8Db();
            u8Db.Connection.Open();
            using (var scope = u8Db.Connection.BeginTransaction())
            {
                try
                {
                    foreach (DbTable model in list)
                    {
                        if (model.editType == DbTable.otypes.edit)
                        {
                            var tableName = model.tableName;
                            var whereDic = model.whereDic;
                            var updateValue = model.updateValue;
                            var query = u8Db.Query(tableName);
                            if (whereDic != null && whereDic.Keys.Count > 0)
                            {
                                foreach (var whereKey in whereDic.Keys)
                                {
                                    var whereValue = whereDic[whereKey];
                                    query.Where(whereKey, "=", whereValue);
                                }
                            }
                            query.Update(updateValue, scope);
                        }
                        else if (model.editType == DbTable.otypes.add)
                        {
                            var tableName = model.tableName;
                            var columns = (List<string>)model.tableColumns;
                            var colValue = (List<IEnumerable<string>>)model.insertValues;
                            u8Db.Query(tableName).Insert(columns, colValue, scope);
                        }
                    }
                    rsl.Result = true;
                    scope.Commit();
                }
                catch (Exception ex)
                {
                    rsl.Result = false;
                    rsl.Message = ex.Message;
                    scope.Rollback();
                }
            }
            return rsl;
        }


        static string connectionStr = "Data Source="+ ConfigurationManager.AppSettings["Server"] +
            ";Initial Catalog=" + ConfigurationManager.AppSettings["DbName"] + 
            ";User ID=" + ConfigurationManager.AppSettings["DbAccount"] + 
            ";Password=" + ConfigurationManager.AppSettings["DbPWD"] + ";Connect Timeout=30";

        /// <summary>
        /// 获取U8数据库中表的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable getData(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string UpdateData(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())//创建命令对象SqlCommand
                {
                    cmd.Connection = conn;//设置连接对象
                    cmd.CommandType = CommandType.Text;//设置Command对象执行语句的类型
                    cmd.CommandText = sql;//设置执行的语句
                    return cmd.ExecuteNonQuery().ToString();//ExecuteNonQuery返回执行SQL语句，受影响的行数
                }
            }
        }

        /// <summary>
        /// 多表事务更新方法
        /// </summary>
        /// <param name="cmdlist">sql集合</param>
        /// <returns></returns>
        public int TransUpdate(List<string> cmdlist, string conn = null)
        {
            int jg = 0;
            try
            {
                using (SqlConnection connection = new SqlConnection())
                {   
                    connection.ConnectionString = connectionStr;
                    connection.Open();
                    using (SqlTransaction myTran = connection.BeginTransaction())//创建事物
                    {
                        try
                        {
                            SqlCommand command = new SqlCommand();
                            command.Transaction = myTran;
                            command.Connection = connection;
                            for (int i = 0; i < cmdlist.Count; i++)
                            {
                                command.CommandType = CommandType.Text;
                                command.CommandText = cmdlist[i].ToString();
                                command.Parameters.Clear();//清空参数集合
                                command.ExecuteNonQuery();
                            }
                            myTran.Commit();
                            jg = 1;
                        }
                        catch (Exception ex)
                        {
                            TxtLogHelper txt = new TxtLogHelper();
                            txt.AddLogMsg("异常错误："+ex.Message);
                            jg = 0;
                            myTran.Rollback();
                            return jg;
                        }

                    }

                }
            }
            catch (Exception ex)
            {
                jg = 0;
                return jg;
            }
            return jg;
        }
    }
}